package cn.shuangze.assetsms.dao.impl;

import cn.shuangze.assetsms.dao.DBUtil;
import cn.shuangze.assetsms.dao.PersonInfoDao;
import cn.shuangze.assetsms.entity.PersonInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import static cn.shuangze.assetsms.dao.DBUtil.exceuteUpdate;
import static cn.shuangze.assetsms.dao.DBUtil.getConnection;

public class PersonInfoDaoJDBCImpl implements PersonInfoDao {

    @Override
    public int add(PersonInfo personInfo) throws Exception {
        String sql = "insert into person(Name,Sex,Dept,Job,Other) values(?,?,?,?,?)";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Object[]{
                    personInfo.getName(),
                    personInfo.getSex(),
                    personInfo.getDept(),
                    personInfo.getJob(),
                    personInfo.getOther()
            });
        }
    }

    @Override
    public int delete(int id) throws Exception {
        String sql = "delete from person where PersonID=?";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Integer[]{id});
        }
    }

    @Override
    public int modify(PersonInfo personInfo) throws Exception {
        String sql = "update person set Name=?,Sex=?,Dept=?,Job=?,Other=? where PersonID=?";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Object[]{
                    personInfo.getName(),
                    personInfo.getSex(),
                    personInfo.getDept(),
                    personInfo.getJob(),
                    personInfo.getOther(),
                    personInfo.getPersonID()
            });
        }
    }

    @Override
    public String[][] findAll() throws Exception {
        return findByCondition(new PersonInfo());

    }

    @Override
    public String[][] findByCondition(PersonInfo personInfo) throws Exception {

        StringBuilder sql = new StringBuilder("select * from person where 1=1");

        if (personInfo == null)
            return null;

        if (personInfo.getPersonID() != 0) {
            sql.append(" and personID =" + personInfo.getPersonID());

        }
        if (personInfo.getName() != null && !personInfo.getName().equals("")) {
            sql.append(" and name regexp '" + personInfo.getName() + "'");

        }
        if (personInfo.getDept() != null && !personInfo.getDept().equals("")) {
            sql.append(" and dept regexp '" + personInfo.getDept() + "'");

        }
        if (personInfo.getJob() != null && !personInfo.getJob().equals("")) {
            sql.append(" and job regexp '" + personInfo.getJob() + "'");

        }


        return getSqlResult(sql.toString());

    }


    private String[][] getSqlResult(String sql) throws Exception {
        List<PersonInfo> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                list.add(new PersonInfo(
                        rs.getInt("PersonID"),
                        rs.getString("Name"),
                        rs.getString("Sex"),
                        rs.getString("Dept"),
                        rs.getString("Job"),
                        rs.getString("Other")
                ));
            }
        }

        String[][] sn = null;
        int size = list.size();
        if (!list.isEmpty()) {
            sn = new String[list.size()][6];
        }
        for (int i = 0; i < size; i++) {
            sn[i][0] = Integer.toString(list.get(i).getPersonID());
            sn[i][1] = list.get(i).getName();
            sn[i][2] = list.get(i).getSex();
            sn[i][3] = list.get(i).getDept();
            sn[i][4] = list.get(i).getJob();
            sn[i][5] = list.get(i).getOther();
        }
        return sn;
    }

}
